from tools.dbo import get_server_data_car, put_mysql_data, get_server_data, carried_sql, get_server_data_one, \
    get_dict_data_sql


# 样本项目同步样本源项目
from tools.tools import get_unit


def syn_sample_sn_resource_project(sql_server_conn, mysql_conn):
    print("===============================正在同步样本源项目到样本==============================")
    sql_resource = "update sample_info si inner join sample_resource sr on si.sample_resource_id = sr.id set si.project_id = sr.project_id where si.project_id = 0;"
    carried_sql(mysql_conn,sql_resource)
    print("===============================样本源项目到样本同步完成==============================")

# 样本数据迁移
# 及时状态 0可入库 1已入库 2 已出库3已销毁 4保留位置出库 5预入库
def syn_sample(sql_server_conn, mysql_conn):
    print("===============================正在迁移样本数据==============================")
    carried_sql(mysql_conn,"TRUNCATE sample_info")
    instantState =  "case instantState when '可入库' then 0 when '已入库' then 1 when '已出库' then 2 when '已销毁' then 3 end"
    sql = "SELECT id,sampleNo,sampleNm,sampleTypeId,"+instantState+",projectId,departId+99 as departId,sampleSourceId,initCapacity,currCapacity,unit,freezeCount,batchNo,isrtEmpId,isrtDt,updtEmpId,updtDt,1,2 FROM AP_SP_Sample"
    return_data = get_server_data_car(sql_server_conn, sql)
    print(put_mysql_data(mysql_conn, "id,code,name,type_id,status,project_id,dept_id,sample_resource_id,initial_capacity,current_capacity,unit,freeze_thaw_times,batch_no,create_id,create_time,update_id,update_time,number,tenant_id", "sample_info", return_data))
    print("===============================样本数据迁移完成==============================")


# 获取样本附加字段倒排矩阵
def get_sample_type_metrix(sql_server_conn):
    sql = "select sampleTypeId,customFieldId,fieldNo from AP_SP_SampleTypeExAttribute where isDel = 0"
    return_data = get_server_data_car(sql_server_conn, sql)
    res = {}
    for item in return_data:
        tmp = {}
        if item[0] in res.keys():
            tmp = res.get(item[0])
        tmp[item[2]] = item[1]
        res[item[0]] = tmp
    return res

# 样本自定义字段迁移
def syn_sample_ex_value(sql_server_conn, mysql_conn):
    print("===============================正在迁移样本扩展数据==============================")
    carried_sql(mysql_conn,"TRUNCATE sample_info_ex_value")
    sample_type_metrix = get_sample_type_metrix(sql_server_conn)
    select_sample_value_sql = "select * from AP_SP_SampleTypeExData "
    cus_value_dict = get_dict_data_sql(sql_server_conn, select_sample_value_sql)
    sample_ex_value_list = []
    for item in cus_value_dict:
        typeId = item["sampleTypeId"]
        if typeId not in sample_type_metrix.keys():
            continue
        fields = sample_type_metrix[item["sampleTypeId"]]
        for k, v in fields.items():
            if item[k] is None or item[k] == "":
                continue
            sample_ex_value_list.append([item["sampleId"],v,item[k],2])
    print(len(sample_ex_value_list))
    print(put_mysql_data(mysql_conn, "info_id,cus_id,cus_value,tenant_id", "sample_info_ex_value", sample_ex_value_list))
    print("===============================样本扩展数据迁移完成==============================")



# 样本类型属性迁移
def syn_sample_type_attr(sql_server_conn, mysql_conn):
    print("===============================正在迁移样本扩展属性==============================")
    carried_sql(mysql_conn,"TRUNCATE sample_type_configuration_ex")
    return_data = get_server_data(sql_server_conn, "2,id,sampleTypeId,customFieldId,groupNm,required,isDel,isrtEmpId,isrtDt,updtEmpId,updtDt","1 = 1","AP_SP_SampleTypeExAttribute")
    print(put_mysql_data(mysql_conn, "tenant_id,id,sample_type_configuration_id,cus_id,group_name,is_requried,is_delete,create_id,create_time,update_id,update_time", "sample_type_configuration_ex", return_data))
    print("===============================样本扩展属性迁移完成==============================")

# 样本类型迁移
# select id,sampleTypeNo,sampleTypeNm,parentId,iconUrl,unit,defaultCapacity,minCapacity,maxStorePeriod,storePeriodUnit,isrtEmpId,isrtDt,updtEmpId,updtDt from AP_SP_SampleType
def syn_sample_type(sql_server_conn, mysql_conn):
    print("===============================正在迁移样本类型数据==============================")
    carried_sql(mysql_conn,"TRUNCATE sample_type_configuration")

    return_data = get_server_data(sql_server_conn, "2,id,sampleTypeNo,sampleTypeNm,parentId,iconUrl,unit,defaultCapacity,minCapacity,isrtEmpId,isrtDt,updtEmpId,updtDt,1","1 = 1","AP_SP_SampleType")
    # print(return_data)
    print(put_mysql_data(mysql_conn, "tenant_id,id,code,name,parent_id,icon,unit,default_quantity,minimum_capacity,create_id,create_time,update_id,update_time,status", "sample_type_configuration", return_data))
    # UPDATE `sample_type_configuration` SET icon = REPLACE ( icon, '/images', '../../../img' ); 增加自动执行
    carried_sql(mysql_conn,"UPDATE `sample_type_configuration` SET icon = REPLACE ( icon, '/images', '../../../img' );")
    #替换中文单位
    change_sample_type_unit(mysql_conn)
    print("===============================样本类型数据迁移完成==============================")


#样本迁移后进行单位替换，若不成功可以手动执行该方法
def change_sample_type_unit(conn):
    dict_unit = get_unit(conn)
    if dict_unit is None:
        return
    for (k,v) in dict_unit:
        sql = "UPDATE `sample_type_configuration` SET unit = %s where unit='%s' "%(str(k),v)
        carried_sql(conn,sql)

    # UPDATE `sample_type_configuration` SET status = 1;  在select中执行，无需单独执行
    # UPDATE `sample_type_configuration` SET unit = REPLACE(unit,"根","4578"); 增加自动执行
    # UPDATE `sample_type_configuration` SET unit = REPLACE(unit,"毫克","4577"); 增加自动执行
    # UPDATE `sample_type_configuration` SET unit = REPLACE(unit,"个","4576"); 增加自动执行
    # UPDATE `sample_type_configuration` SET unit = REPLACE(unit,"克","4575"); 增加自动执行
    # UPDATE `sample_type_configuration` SET unit = REPLACE(unit,"微升","4574"); 增加自动执行
    # UPDATE `sample_type_configuration` SET unit = REPLACE(unit,"毫升","4573"); 增加自动执行
    # UPDATE `sample_type_configuration` SET unit = REPLACE(unit,"块","4572"); 增加自动执行
    # UPDATE `sample_type_configuration` SET unit = REPLACE(unit,"片","4571");增加自动执行
